"""
Case Type   : SQL
Case Name   : 创建列存表指定compression观察压缩效果
Create At   : 2023/04/17
Owner       : cr13
Description :
    1.创建列存表,不指定compression
    2.创建列存表,指定compression不同值
    3.批量向表1,表2,表3插入数据
    4.\d+ 查询3表大小并比较
    5.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.compression=no压缩效果最差,compression=high压缩效果最好
    5.成功
History     :
"""

import os
import unittest
from testcase.utils.Common import CommonSH
from testcase.utils.Logger import Logger
from testcase.utils.Constant import Constant


class SQL(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.commsh = CommonSH('PrimaryDbUser')
        self.con = Constant()
        self.tb_name1 = 't_column_compression_0334_01'
        self.tb_name2 = 't_column_compression_0334_02'
        self.tb_name3 = 't_column_compression_0334_03'

    def test_column_compression(self):
        text = "----step1:创建列存表,不指定compression expect:成功-----"
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name1};create table " \
            f"{self.tb_name1} (col1 integer delta unique not null,col2 " \
            f"int4 delta unique not null,col3 int4 delta unique not null," \
            f"col4 int8 delta unique not null) with(orientation=column);"
        result = self.commsh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.con.TABLE_DROP_SUCCESS, result, '执行失败' +
                      text)
        self.assertIn(self.con.TABLE_CREATE_SUCCESS, result, '执行失败' +
                      text)

        text = "----step2:创建列存表,指定compression不同值 expect:成功-----"
        self.log.info(text)
        sql_cmd = f"drop table if exists {self.tb_name2},{self.tb_name3};" \
            f"create table {self.tb_name2} (col1 integer delta unique not " \
            f"null,col2 int4 delta unique not null,col3 int4 delta unique " \
            f"not null,col4 int8 delta unique not null) " \
            f"with(orientation=column, compression = high);" \
            f"create table {self.tb_name3} (col1 integer delta unique not " \
            f"null,col2 int4 delta unique not null,col3 int4 delta unique " \
            f"not null,col4 int8 delta unique not null) " \
            f"with(orientation=column, compression = no);"
        result = self.commsh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.con.TABLE_DROP_SUCCESS, result, '执行失败' +
                      text)
        self.assertEqual(result.count(self.con.TABLE_CREATE_SUCCESS), 10,
                         '执行失败' + text)

        text = "----step3:批量向表1,表2,表3插入数据 expect:成功----"
        self.log.info(text)
        sql_cmd = f"insert into  {self.tb_name1} select i,i,i,i from " \
            f"generate_series(1,100000) as i;" \
            f"insert into  {self.tb_name2} select i,i,i,i from " \
            f"generate_series(1,100000) as i;" \
            f"insert into  {self.tb_name3} select i,i,i,i from " \
            f"generate_series(1,100000) as i;"
        result = self.commsh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertEqual(result.count(self.con.INSERT_SUCCESS_MSG), 3,
                         '执行失败' + text)
        
        text = r"----step4:\d+ 查询3表大小并比较 expect:compression=no压缩" \
               "效果最差,compression=high压缩效果最好----"
        self.log.info(text)
        sql_cmd = "\\d+"
        result = self.commsh.execut_db_sql(sql_cmd)
        self.log.info(result)
        try:
            result_split1 = str(result).splitlines()[3]
            result_split2 = str(result).splitlines()[4]
            result_split3 = str(result).splitlines()[5]
            size_default = int(result_split1.split()[-5])
            size_high = int(result_split2.split()[-5])
            size_no = int(result_split3.split()[-5])
            self.assertTrue(size_no > size_default > size_high, '执行失败' +
                            text)
        except Exception as e:
                self.log.info('执行异常', e)

    def tearDown(self):
        text = '-----step5:清理环境 expect:成功-----'
        self.log.info(text)
        result = self.commsh.execut_db_sql(
            f"drop table if exists {self.tb_name1},{self.tb_name2},"
            f"{self.tb_name3};")
        self.log.info(result)
        self.assertIn(self.con.TABLE_DROP_SUCCESS, result, '执行失败' +
                      text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
